﻿using Dapper;
using InvoiceImport.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace FileConver.DAL
{
    /// <summary>
    /// 数据库访问助手
    /// </summary>
    public static class DataHelper
    {
        /// <summary>
        /// 静态构造方法
        /// </summary>
        static DataHelper()
        {
            //从配置文件中读取数据库连接字符串
            DataHelper.ConnectionString =
                 System.Configuration.ConfigurationManager
                .ConnectionStrings["conn"].ConnectionString;
        }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionString { get; set; }

        /// <summary>
        /// 执行增删改SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(DataHelper.ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (pars != null)
                    cmd.Parameters.AddRange(pars);
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行查询SQL语句，并返回数据读取器。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>数据读取器</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(DataHelper.ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (pars != null)
                cmd.Parameters.AddRange(pars);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行查询SQL语句，并返回数据集。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataSet(string sql, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(DataHelper.ConnectionString);
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            if (pars != null)
                sda.SelectCommand.Parameters.AddRange(pars);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 查询币种
        /// </summary>
        public static List<CurrencyModel> QueryCurrency(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT cexch_code,cexch_name FROM foreigncurrency
                string sql = "SELECT t1.cexch_name,cexch_code,itype ,iperiod ,iYear,nflat  FROM foreigncurrency t1 LEFT JOIN exch t2 on t1.cexch_name=t2.cexch_name  WHERE itype=2 ORDER BY iYear DESC";
                return conn.Query<CurrencyModel>(sql).ToList();
            }
        }
        /// <summary>
        /// 查询凭证类别
        /// </summary>
        public static List<VoucherTypeModel> QueryVoucherType(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT csign,ctext FROM dsign
                return conn.Query<VoucherTypeModel>("SELECT csign, isignseq,ctext FROM dsign").ToList();

            }
        }
        /// <summary>
        /// 查询当月最大凭证号
        /// </summary>
        /// <param name="iperiod"></param>
        /// <param name="csign"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static int QueryMaxVoucherNo(string dbill_date,string iperiod, string csign, string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT csign,ctext FROM dsign

                string sql = $"select isnull(Max(ino_id),0) from GL_accvouch where iyear={dbill_date} AND iperiod={iperiod} and csign='{csign}' and ino_id is not null";
                return conn.Query<int>(sql).FirstOrDefault();
            }
        }

        /// <summary>
        /// 查询科目编码
        /// </summary>
        public static List<CodeModel> QueryCode(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT ccode,bproperty,bperson,bcus,bsup,bitem,bdept FROM code
                string sql = "SELECT ccode,ccode_name,bproperty,bend,bperson,bcus,bsup,bitem,bdept FROM code";
                return conn.Query<CodeModel>(sql).ToList();
            }
        }
        /// <summary>
        /// 查询部门档案
        /// </summary>
        public static List<DepartmentModel> QueryDepartment(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT cDepCode,cDepName FROM Department
                return conn.Query<DepartmentModel>("SELECT cDepCode,cDepName FROM Department").ToList();
            }
        }
        /// <summary>
        /// 人员档案
        /// </summary>
        public static List<PersonModel> QueryPerson(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT cPsn_Num,cPsn_Name FROM hr_hi_person 
                return conn.Query<PersonModel>("SELECT cPsn_Num,cPsn_Name,cDept_num FROM hr_hi_person ").ToList();
            }
        }
        /// <summary>
        /// 客户档案
        /// </summary>
        public static List<CustomersModel> QueryCustomer(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT cCusCode, cCusName FROM Customer  //客户档案
                return conn.Query<CustomersModel>("SELECT cCusCode, cCusName FROM Customer").ToList();
            }
        }
        /// <summary>
        /// 供应商档案
        /// </summary>
        public static List<VendorModel> QueryVendor(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //SELECT cVenCode, cVenName FROM Vendor  //供应商档案
                return conn.Query<VendorModel>("SELECT cVenCode, cVenName FROM Vendor").ToList();
            }
        }
        /// <summary>
        /// 项目档案
        /// </summary>
        public static List<ProjectModel> QueryProject(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                string sql = $"select citemcode,citemname,bclose,citemccode from fItemss00";
                return conn.Query<ProjectModel>(sql).ToList();
            }
        }
        /// <summary>
        /// 导入凭证
        /// </summary>
        /// <param name="connStr">字符串</param>
        /// <param name="gls">数据源</param>
        /// <returns></returns>
        public static int InsertVouchers(string connStr, List<GlAccvouchModel> gls)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine("INSERT INTO  GL_accvouch");
            builder.AppendLine("(iperiod,csign,isignseq,ino_id,inid,idoc,dbill_date,cbill,ibook,cdigest,ccode,cexch_name,md,mc,");
            builder.AppendLine("md_f,mc_f,nfrat,nd_s,nc_s,iyear,iYPeriod,cdept_id,cperson_id,ccus_id,csup_id,citem_id,");
            builder.AppendLine("doutbilldate,bvouchedit,bvalueedit,bcodeedit,bPCSedit,bDeptedit,bItemedit,cname,Citem_class)");
            builder.AppendLine("VALUES");
            builder.AppendLine("(@iperiod,@csign,@isignseq,@ino_id,@inid,@idoc,@dbill_date,@cbill,@ibook,@cdigest,@ccode,@cexch_name,@md,@mc,");
            builder.AppendLine("@md_f,@mc_f,@nfrat,@nd_s,@nc_s,@iyear,@iYPeriod,@cdept_id,@cperson_id,@ccus_id,@csup_id,@citem_id,");
            builder.AppendLine("@doutbilldate,@bvouchedit,@bvalueedit,@bcodeedit,@bPCSedit,@bDeptedit,@bItemedit,@cname,@Citem_class)");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        var result = conn.Execute(builder.ToString(), gls, tran);
                        tran.Commit();
                        return result;
                    }
                    catch (Exception er)
                    {
                        tran.Rollback();
                        throw new Exception("SaveError" + er.Message);
                    }
                }
            }
        }

    }
}
